﻿CREATE PROCEDURE [dbo].[usp_select_Fault_Count_By_ServiceName]
(
        @Application       varchar(50)      = NULL
	   ,@ServiceName	   varchar(256)		= NULL
	   ,@ErrorType		   varchar(100)		= NULL
	   ,@StartDate         datetime         = NULL
	   ,@EndDate           datetime         = NULL
	   ,@FaultCode         varchar(50)      = NULL
	   ,@FailureCategory   varchar(50)      = NULL
	   ,@ExceptionType     varchar(50)      = NULL
	   ,@MinFaultSeverity  int              = NULL
	   ,@MaxFaultSeverity  int              = NULL
	   ,@Debug             bit              = 0
)
AS

/****************************************************************************
Name: usp_select_Fault_Count_By_ServiceName.sql
*****************************************************************************/
SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10);
DECLARE @ErrorCode         int;

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: ';

-- Print parameters
IF (@Debug > 0)
BEGIN
    PRINT '@Application = ' + @Application
	PRINT '@ServiceName = ' + @ServiceName
	PRINT '@ErrorType = ' + @ErrorType
	PRINT '@StartDate = ' + CAST (@StartDate as varchar)
	PRINT '@EndDate = ' + CAST (@EndDate as varchar)
	PRINT '@FaultCode = ' + @FaultCode
	PRINT '@FailureCategory = ' + @FailureCategory
	PRINT '@ExceptionType = ' + @ExceptionType
	PRINT '@MinFaultSeverity = ' + CAST (@MinFaultSeverity as varchar)
	PRINT '@MaxFaultSeverity = ' + CAST (@MaxFaultSeverity as varchar)
END;

SELECT
	SUM(af.[FaultCount]) AS FaultCount,
	af.[ServiceName]

FROM dbo.vw_AggregatedFaults af

WHERE 
		 af.[Application] = ISNULL(@Application, af.[Application])
   AND	 af.[ServiceName] = ISNULL(@ServiceName, af.[ServiceName])
   AND   af.[ErrorType] = ISNULL(@ErrorType, af.[ErrorType])
   AND   af.[FailureCategory] = ISNULL(@FailureCategory, af.[FailureCategory])
   AND   af.[ExceptionType] = ISNULL(@ExceptionType, af.[ExceptionType])
   AND   af.[FaultCode] = ISNULL(@FaultCode, af.[FaultCode])
   AND   (af.[DateTime] between ISNULL(@StartDate, af.[DateTime]) AND ISNULL(@EndDate, af.[DateTime]))
   AND   (af.[FaultSeverity] between ISNULL(@MinFaultSeverity, af.[FaultSeverity]) AND ISNULL(@MaxFaultSeverity, af.[FaultSeverity]))

GROUP BY af.[ServiceName]

-- CHECK FOR ERROR
SET @ErrorCode = @@Error;
IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
			
RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;

SET NOCOUNT OFF;
